﻿using System;
using System.Xml;

namespace sqlserverextensions.ExportOperations
{
    internal class ExcelXml : System.IDisposable
    {
        private Boolean disposedValue;
        private XmlWriter writer;

        internal ExcelXml(string fileName)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            settings.Indent = true;
            settings.NewLineOnAttributes = true;

            this.writer = XmlWriter.Create(fileName, settings);
            this.writer.WriteStartDocument();
        }

        internal void Close()
        {
            if(writer!=null)
            {
                this.writer.WriteEndDocument();
                this.writer.Flush();
                this.writer.Close();
                this.writer = null;
            }
        }

        internal void WorkbookStart()
        {
            //// Excel Xml Header zur FileType Kennung
            this.writer.WriteProcessingInstruction("mso-application", "progid=\"Excel.Sheet\"");

            //// Workbook Element
            this.writer.WriteStartElement("Workbook", "urn:schemas-microsoft-com:office:spreadsheet");
            this.writer.WriteAttributeString("xmlns", "o", null, "urn:schemas-microsoft-com:office:office");
            this.writer.WriteAttributeString("xmlns", "x", null, "urn:schemas-microsoft-com:office:excel");
            this.writer.WriteAttributeString("xmlns", "ss", null, "urn:schemas-microsoft-com:office:Spreadsheet");
            this.writer.WriteAttributeString("xmlns", "html", null, "http://www.w3.org/TR/REC-html40");

            //// Dokumenten Eigenschaften.
            this.writer.WriteStartElement("DocumentProperties");

            this.writer.WriteStartElement("Version", "12.00");
            this.writer.WriteEndElement();   // Version
            this.writer.WriteEndElement();   // DocumentProperties

            //// Format-Definitionen
            this.writer.WriteStartElement("Styles");

            //// Format Normal (für Daten)
            this.writer.WriteStartElement("Style");
            this.writer.WriteAttributeString("ss", "ID", null, "Default");
            this.writer.WriteAttributeString("ss", "Name", null, "Normal");
            this.writer.WriteElementString("Borders", "");
            this.writer.WriteElementString("Font", "");
            this.writer.WriteElementString("Interior", "");
            this.writer.WriteElementString("NumberFormat", "");
            this.writer.WriteElementString("Protection", "");
            this.writer.WriteEndElement();   //Style

            //// Format Überschrift
            this.writer.WriteStartElement("Style");
            this.writer.WriteAttributeString("ss", "ID", null, "Header");
            this.writer.WriteAttributeString("ss", "Name", null, "Header");
            this.writer.WriteStartElement("Font");
            this.writer.WriteAttributeString("ss", "Bold", null, "1");
            this.writer.WriteEndElement();     // Font
            this.writer.WriteElementString("Borders", "");
            this.writer.WriteElementString("Interior", "");
            this.writer.WriteElementString("NumberFormat", "");
            this.writer.WriteElementString("Protection", "");
            this.writer.WriteEndElement();    //Style

            this.writer.WriteEndElement();   //Styles

            this.writer.Flush();
        }

        internal void WorkbookEnd()
        {
            this.writer.WriteEndElement();
            this.writer.Flush();
        }

        internal void WorksheetStart(string name)
        {
            this.writer.WriteStartElement("Worksheet");
            this.writer.WriteAttributeString("ss", "Name", null, name);

            this.writer.WriteStartElement("Table");
            this.writer.WriteAttributeString("ss", "FullColumns", null, "1");
            this.writer.WriteAttributeString("ss", "FullRows", null, "1");
            this.writer.WriteAttributeString("ss", "DefaultColumnWidth", null, "60");
        }

        internal void WorksheetEnd()
        {
            this.writer.WriteEndElement();   // Table
            this.writer.WriteStartElement("WorksheetOptions", "urn:schemas-microsoft-com:office:spreadsheet");
            this.writer.WriteAttributeString("xmlns", "o", null, "urn:schemas-microsoft-com:office:office");

            this.writer.WriteStartElement("PageSetup");
            this.writer.WriteEndElement();    // PageSetup

            this.writer.WriteEndElement();    // WorksheetOptions
            this.writer.WriteEndElement();    // WorkSheet

        }

        internal void RowStart()
        {
            this.writer.WriteStartElement("Row");
        }

        internal void RowEnd()
        {
            this.writer.WriteEndElement();
            this.writer.Flush();
        }

        internal void WriteHeaderCell(String name)
        {
            this.writer.WriteStartElement("Cell");
            this.writer.WriteAttributeString("ss", "StyleID", null, "Header");
            this.writer.WriteStartElement("Data");
            this.writer.WriteAttributeString("ss", "Type", null, "String");
            this.writer.WriteString(name);
            this.writer.WriteEndElement(); // Data
            this.writer.WriteEndElement(); // Cell
        }

        internal void WriteDataCell(String data)
        {
            this.writer.WriteStartElement("Cell");
            this.writer.WriteStartElement("Data");
            this.writer.WriteAttributeString("ss", "Type", null, "String");
            this.writer.WriteString(data);
            this.writer.WriteEndElement(); // Data
            this.writer.WriteEndElement(); // Cell
        }

        protected void Dispose(Boolean disposing)
        {
            if (!this.disposedValue)
            {
                if (disposing)
                {
                    if (this.writer != null)
                    {
                        this.Close();
                        this.writer = null;
                    }
                }
            }
            this.disposedValue = true;
        }

        public void Dispose() //Implements IDisposable.Dispose
        {
            // Ändern Sie diesen Code nicht. Fügen Sie oben in Dispose(ByVal disposing As Boolean) Bereinigungscode ein.
            Dispose(true);
            GC.SuppressFinalize(this);
        }

    }
}